package datalayer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import Model.Nummer;

public class DBobject {
	
	private String url = "jdbc:sqlite:./database.sqlite";

	protected static Connection conn;
	
	
	public DBobject() {
		try {
			Class.forName("org.sqlite.JDBC").newInstance();
			conn = (Connection) DriverManager.getConnection(url);
			System.err.println("connected to database");

		} catch (Exception e) {
			System.out.println(e.getMessage());
			JOptionPane.showMessageDialog(null,"Kan geen verbinding met Database maken!");
		}
	}
	
	public void initDB(){

		String sql = "CREATE TABLE IF NOT EXISTS 'Song' ('id' INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE  DEFAULT 0, 'name' TEXT DEFAULT null, 'path' TEXT DEFAULT null, 'rating' INTEGER DEFAULT null, 'genre' TEXT DEFAULT null, 'lenght' DOUBLE DEFAULT null, 'artist' TEXT DEFAULT null, 'album' TEXT DEFAULT null, 'year' INTEGER DEFAULT null);";
//		String Album= "CREATE TABLE IF NOT EXISTS 'Artist' ('ArtistID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE DEFAULT 0, 'Name' TEXT)";
//		String Genre = "CREATE TABLE IF NOT EXISTS 'Genre' ('GenreID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE DEFAULT 0, 'Name' TEXT)";
//		String Artist = "CREATE TABLE IF NOT EXISTS 'Album' ('AlbumID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE DEFAULT 0, 'Name' TEXT)";
		//String 

		Statement stmt;
		
		try {
			stmt = conn.createStatement();
//			stmt.executeUpdate(Album);
//			stmt.executeUpdate(Genre);
//			stmt.executeUpdate(Artist);
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void removeSong(Nummer song){
		int songID = song.getId();
		
		Statement stmt;
		try {
			
			stmt = conn.createStatement();
			
			String sql = "DELETE FROM song WHERE id = " + songID;
					
			stmt.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		}	
		
	}
	
	public void saveDB(Nummer song){
		Statement stmt;
		try {
			
			stmt = conn.createStatement();
			
			String sql = "INSERT INTO Song(name,path,rating,genre,lenght,year,album,artist) VALUES ('" +
					song.getNaam()+"','"+
					song.getLocatie()+"','"+
					song.getRating()+"','"+
					song.getGenre()+"','"+
					song.getTijdsduur()+"','"+
					song.getJaar() +"','"+
					song.getAlbum()+"','"+
					song.getArtiest()+
					"')"
				
					
					;
					
			stmt.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
	public Nummer loadNummerDB(Nummer n){
		
		return n;
	}
	
	public ArrayList<Nummer> loadDB(){
		Statement stmt;
		Nummer s;
		ArrayList<Nummer> songs = new ArrayList<Nummer>();
		
		try {
			stmt = conn.createStatement();
			String sql = "Select * from song";

			ResultSet rsltst = stmt.executeQuery(sql);
			
			while (rsltst.next()) {		
				s = new Nummer(0,"","","",0.0,0,0,0,"","");
				s.setId(rsltst.getInt(1));
				s.setNaam(rsltst.getString(2));
				s.setGenre(rsltst.getString(5));
				s.setLocatie(rsltst.getString(3));
				s.setRating(rsltst.getInt(4));
				s.setJaar(rsltst.getString(9));
				s.setTijdsduur(rsltst.getDouble(6));
				s.setAlbum(rsltst.getString(8));
				s.setArtiest(rsltst.getString(7));
				
				songs.add(s);
			}

			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return songs;			
	}

}
